﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Synchronization.Data;
using log4net;
using SyncLib.Data;
using SyncLib.Interface;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization;
using System.Reflection;
using System.IO;

namespace SyncLib
{

    public class KeeperSyncUtility
    {
        public static ILog Log = LogManager.GetLogger(typeof(Log4NetImplement));
        public const string Solve_Auto = "Auto";
        public const string Solve_CallProc = "CallProc";
        public const string Solve_ServerWin = "ServerWin";
        public const string Solve_ClientWin = "ClientWin";
        public const string Solve_Manual = "Manual";
        public const string Solve_NoneSpecified = "None";

        public static void CheckParameterNull(object p, string name, string des)
        {
            if (p == null)
                throw new Exception(name + " can not be null:" + des);
        }
        public static IKeeperSync CreateKeeperSync(ScopeInfoEx scope, string baseDirectory, int type, out string classPath)
        {
            classPath = scope.ClientClass.Trim() + "," + Path.GetFileNameWithoutExtension(scope.ClientAssembly.Trim());
            string assPath = Path.Combine(baseDirectory, scope.ClientAssembly);
            string className = scope.ClientClass;
            string assemblyName = scope.ClientAssembly;
            if (type == 1)
            {
                className = scope.ServerClass;
                assemblyName = scope.ServerAssembly;
                classPath = scope.ServerClass.Trim() + "," + Path.GetFileNameWithoutExtension(scope.ServerAssembly.Trim());
                assPath = Path.Combine(baseDirectory, scope.ServerAssembly);

            }



            if (File.Exists(assPath))
            {
                Assembly ass = Assembly.LoadFile(assPath);
                Type t = ass.GetType(type == 1 ? scope.ServerClass : scope.ClientClass);
                if (t == null)
                    throw new Exception(
                        string.Format("Can't get type {0} in assembly {1}. Please contact to administrator!",
                           classPath, assemblyName));
                else
                {
                    object objTemp = Activator.CreateInstance(t);
                    IKeeperSync obj = (IKeeperSync)objTemp;
                    return obj;
                }

            }
            else
            {
                throw new FileNotFoundException("CreateKeeperSync :", assemblyName);
            }

        }
        public static void SaveSyncLog(DateTime start, DateTime end, SyncOperationStatistics syncStatis, KSyncStatistics statics, KSyncStatistics remoteStatics, ScopeInfoEx scope, string result, string runMode, string comment)
        {
            statics = statics ?? new KSyncStatistics();
            statics.CheckValue();
            remoteStatics = remoteStatics ?? new KSyncStatistics();
            remoteStatics.CheckValue();
            using (SqlConnection Connection = new SqlConnection(CommonVariable.SqlConnectionString))
            {
                Connection.Open();
                using (SqlTransaction Transaction = Connection.BeginTransaction())
                {
                    using (SqlCommand cmdHistorySync = CreateInsertHistorySyncCommand(Connection, Transaction))
                    {
                        cmdHistorySync.Parameters["@SyncHistoryId"].Value = statics.SessionId;
                        cmdHistorySync.Parameters["@CopeName"].Value =scope.ScopeName;
                        cmdHistorySync.Parameters["@SyncDate"].Value = start;
                        cmdHistorySync.Parameters["@DownloadTotal"].Value = statics.ScopeProcess.TotalChanges;
                        cmdHistorySync.Parameters["@DownloadApplied"].Value = statics.ScopeProcess.TotalChangesApplied;

                        cmdHistorySync.Parameters["@DownloadFailed"].Value = statics.ScopeProcess.TotalChangesFailed;
                        cmdHistorySync.Parameters["@UploadTotal"].Value = remoteStatics.ScopeProcess.TotalChanges;
                        cmdHistorySync.Parameters["@UploadApplied"].Value = remoteStatics.ScopeProcess.TotalChangesApplied;
                        cmdHistorySync.Parameters["@UploadFailed"].Value = remoteStatics.ScopeProcess.TotalChangesFailed;

                        cmdHistorySync.Parameters["@Conflict"].Value = statics.ConflictDatas.Count + remoteStatics.ConflictDatas.Count;
                        cmdHistorySync.Parameters["@Result"].Value = result;
                        cmdHistorySync.Parameters["@SchemaVersion"].Value = scope.ClientSchemaVersion;
                        cmdHistorySync.Parameters["@ScopeVersion"].Value = scope.ClientScopeVersion;
                        cmdHistorySync.Parameters["@ClientId"].Value = scope.ClientID;
                        cmdHistorySync.Parameters["@EndDate"].Value = end;
                        cmdHistorySync.Parameters["@Comment"].Value = comment;
                        cmdHistorySync.Parameters["@RunMode"].Value = runMode;
                        cmdHistorySync.Parameters["@Reviewed"].Value = false;
                        cmdHistorySync.Parameters["@ClientClass"].Value = scope.ClientClass + "[" + scope.ClientAssembly + "]";
                        cmdHistorySync.Parameters["@ServerClass"].Value = scope.ServerClass + "[" + scope.ServerAssembly + "]";
                        cmdHistorySync.ExecuteNonQuery();
                        using (SqlCommand cmdTable = CreateInsertHistoryTableCommand(Connection, Transaction))
                        {
                            foreach (TableOfScope tos in scope.TableOfScopes)
                            {
                                DbSyncTableProgress remoteTable = remoteStatics.ScopeProcess.FindTableProgress(tos.TableName);
                                if (remoteTable == null)
                                    remoteTable = new DbSyncTableProgress(tos.TableName);
                                DbSyncTableProgress table = statics.ScopeProcess.FindTableProgress(tos.TableName);
                                if (table == null)
                                    table = new DbSyncTableProgress(tos.TableName);

                                cmdTable.Parameters["@SyncHistoryId"].Value = statics.SessionId;
                                cmdTable.Parameters["@TableName"].Value = table.TableName;
                                cmdTable.Parameters["@DownloadTotal"].Value = table.TotalChanges;
                                cmdTable.Parameters["@DownloadApplied"].Value = table.ChangesApplied;
                                cmdTable.Parameters["@DownloadFailed"].Value = table.ChangesFailed;
                                cmdTable.Parameters["@UploadTotal"].Value = remoteTable.TotalChanges;
                                cmdTable.Parameters["@UploadApplied"].Value = remoteTable.ChangesApplied;
                                cmdTable.Parameters["@UploadFailed"].Value = remoteTable.ChangesFailed;
                                cmdTable.Parameters["@Conflict"].Value = statics.ConflictDatas.Count + remoteStatics.ConflictDatas.Count;
                                cmdTable.Parameters["@DownloadDelete"].Value = table.Deletes;
                                cmdTable.Parameters["@UploadDelete"].Value = remoteTable.Deletes;
                                cmdTable.Parameters["@DownloadUpdate"].Value = table.Updates;
                                cmdTable.Parameters["@UploadUpdate"].Value = remoteTable.Updates;
                                cmdTable.Parameters["@DownloadInsert"].Value = table.Inserts;
                                cmdTable.Parameters["@UploadInsert"].Value = remoteTable.Inserts;
                                cmdTable.ExecuteNonQuery();


                                if (tos.DependencyTables != null && tos.DependencyTables.Length > 0)
                                {
                                    DbSyncScopeProgress localDepenProcess = null;
                                    DbSyncScopeProgress remoteDepenProcess = null;
                                    if (statics.DependencyStatistic.ContainsKey(tos.TableName))
                                        localDepenProcess = statics.DependencyStatistic[tos.TableName];
                                    else
                                        localDepenProcess = new DbSyncScopeProgress();

                                    if (remoteStatics.DependencyStatistic.ContainsKey(tos.TableName))
                                        remoteDepenProcess = remoteStatics.DependencyStatistic[tos.TableName];
                                    else
                                        remoteDepenProcess = new DbSyncScopeProgress();

                                    using (SqlCommand cmdDepend = CreateInsertDepenCommand(Connection, Transaction))
                                    {
                                        foreach (DependencyTable depen in tos.DependencyTables)
                                        {
                                            DbSyncTableProgress localDepen = localDepenProcess.FindTableProgress(depen.TableName);
                                            if (localDepen == null)
                                                localDepen = new DbSyncTableProgress();

                                            DbSyncTableProgress remoteDepen = remoteDepenProcess.FindTableProgress(depen.TableName);
                                            if (remoteDepen == null)
                                                remoteDepen = new DbSyncTableProgress();

                                            cmdDepend.Parameters["@SyncHistoryId"].Value = statics.SessionId;
                                            cmdDepend.Parameters["@TableName"].Value = tos.TableName;
                                            cmdDepend.Parameters["@DependencyTable"].Value = depen.TableName;
                                            cmdDepend.Parameters["@DownloadTotal"].Value = localDepen.TotalChanges;
                                            cmdDepend.Parameters["@DownloadApplied"].Value = localDepen.ChangesApplied;
                                            cmdDepend.Parameters["@DownloadFailed"].Value = localDepen.ChangesFailed;
                                            cmdDepend.Parameters["@UploadTotal"].Value = remoteDepen.TotalChanges;
                                            cmdDepend.Parameters["@UploadApplied"].Value = remoteDepen.ChangesApplied;
                                            cmdDepend.Parameters["@UploadFailed"].Value = remoteDepen.ChangesFailed;
                                            cmdDepend.Parameters["@Conflict"].Value = 0;
                                            cmdDepend.Parameters["@DownloadDelete"].Value = localDepen.Deletes;
                                            cmdDepend.Parameters["@UploadDelete"].Value = remoteDepen.Deletes;
                                            cmdDepend.Parameters["@DownloadUpdate"].Value = localDepen.Updates;
                                            cmdDepend.Parameters["@UploadUpdate"].Value = remoteDepen.Updates;
                                            cmdDepend.Parameters["@DownloadInsert"].Value = localDepen.Inserts;
                                            cmdDepend.Parameters["@UploadInsert"].Value = remoteDepen.Inserts;
                                            cmdDepend.ExecuteNonQuery();

                                        }
                                    }
                                    //Log Conflicts
                                    using (SqlCommand cmdConflictLog = CreateInsertConflictLogCommand(Connection, Transaction))
                                    {
                                        Dictionary<string, List<KSyncConflict>> conflicts = null;
                                        if (scope.SyncDirection == SyncDirectionOrder.Download || scope.SyncDirection == SyncDirectionOrder.DownloadAndUpload)
                                            conflicts = statics.ConflictDatas;
                                        else
                                            conflicts = remoteStatics.ConflictDatas;
                                        if (conflicts.ContainsKey(tos.TableName))
                                        {
                                            List<KSyncConflict> lstConficts = conflicts[tos.TableName];

                                            foreach (KSyncConflict item in lstConficts)
                                            {

                                                cmdConflictLog.Parameters["@ConflictId"].Value = Guid.NewGuid().ToString();
                                                cmdConflictLog.Parameters["@SyncId"].Value = statics.SessionId;
                                                cmdConflictLog.Parameters["@TableName"].Value = tos.TableName;
                                                cmdConflictLog.Parameters["@LocalData"].Value = item.LocalChange.GetXml();
                                                cmdConflictLog.Parameters["@RemoteData"].Value = item.RemoteChange.GetXml();
                                                cmdConflictLog.Parameters["@SolveType"].Value = tos.SolveConfigType;
                                                cmdConflictLog.Parameters["@Action"].Value = item.Action;
                                                cmdConflictLog.Parameters["@IsSolve"].Value = item.IsSolve;
                                                cmdConflictLog.Parameters["@UserSolve"].Value = DBNull.Value;
                                                cmdConflictLog.Parameters["@Comment"].Value = item.Description;
                                                cmdConflictLog.Parameters["@ConflictType"].Value = item.Type;
                                                cmdConflictLog.Parameters["@SyncState"].Value = item.Stage;
                                                cmdConflictLog.ExecuteNonQuery();
                                            }
                                        }

                                    }
                                }

                            }

                        }
                    }
                    Transaction.Commit();
                }
            }
        }

        private static SqlCommand CreateInsertConflictLogCommand(SqlConnection conn, SqlTransaction trann)
        {
            SqlCommand cmdConflictLog = new SqlCommand("", conn, trann);
            cmdConflictLog.CommandText = @"INSERT INTO [dbo].[ConflictLogs] ([ConflictId], [SyncId], [TableName], [LocalData], [RemoteData], [SolveType], [Action], [IsSolve], [UserSolve], [Comment],ConflictType,SyncState) VALUES (@ConflictId, @SyncId, @TableName, @LocalData, @RemoteData, @SolveType, @Action, @IsSolve, @UserSolve, @Comment,@ConflictType,@SyncState)";
            cmdConflictLog.CommandType = global::System.Data.CommandType.Text;
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ConflictId", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "ConflictId", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SyncId", global::System.Data.SqlDbType.UniqueIdentifier, 0, global::System.Data.ParameterDirection.Input, 0, 0, "TableName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@TableName", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "TableId", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@LocalData", global::System.Data.SqlDbType.Xml, 0, global::System.Data.ParameterDirection.Input, 0, 0, "LocalData", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RemoteData", global::System.Data.SqlDbType.Xml, 0, global::System.Data.ParameterDirection.Input, 0, 0, "RemoteData", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SolveType", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "SolveType", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Action", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Action", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@IsSolve", global::System.Data.SqlDbType.Bit, 0, global::System.Data.ParameterDirection.Input, 0, 0, "IsSolve", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UserSolve", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UserSolve", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Comment", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Comment", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ConflictType", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "ConflictType", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            cmdConflictLog.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SyncState", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "SyncState", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            return cmdConflictLog;

        }
        private static SqlCommand CreateInsertHistoryTableCommand(SqlConnection conn, SqlTransaction trann)
        {
            SqlCommand command = new SqlCommand("", conn, trann);
            command.CommandText = @"INSERT INTO [dbo].[HistoryTableSync] ([SyncHistoryId], [TableName], [DownloadTotal], [DownloadApplied], [DownloadFailed], [UploadTotal], [UploadApplied], [UploadFailed], [Conflict], [DownloadDelete], [UploadDelete], [DownloadUpdate], [UploadUpdate], [DownloadInsert], [UploadInsert]) VALUES (@SyncHistoryId, @TableName, @DownloadTotal, @DownloadApplied, @DownloadFailed, @UploadTotal, @UploadApplied, @UploadFailed, @Conflict, @DownloadDelete, @UploadDelete, @DownloadUpdate, @UploadUpdate, @DownloadInsert, @UploadInsert);
SELECT SyncHistoryId, TableName, DownloadTotal, DownloadApplied, DownloadFailed, UploadTotal, UploadApplied, UploadFailed, Conflict, DownloadDelete, UploadDelete, DownloadUpdate, UploadUpdate, DownloadInsert, UploadInsert FROM HistoryTableSync WHERE (SyncHistoryId = @SyncHistoryId) AND (TableName = @TableName)";
            command.CommandType = global::System.Data.CommandType.Text;
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SyncHistoryId", global::System.Data.SqlDbType.UniqueIdentifier, 0, global::System.Data.ParameterDirection.Input, 0, 0, "SyncHistoryId", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@TableName", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "TableName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadTotal", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadTotal", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadApplied", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadApplied", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadFailed", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadFailed", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadTotal", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadTotal", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadApplied", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadApplied", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadFailed", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadFailed", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Conflict", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Conflict", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadDelete", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadDelete", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadDelete", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadDelete", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadUpdate", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadUpdate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadUpdate", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadUpdate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadInsert", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadInsert", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadInsert", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadInsert", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            return command;
        }

        private static SqlCommand CreateInsertHistorySyncCommand(SqlConnection conn, SqlTransaction tran)
        {
            SqlCommand command = new SqlCommand(
                @"INSERT INTO [HistorySync] 
                               ([SyncHistoryId], [CopeName],[SyncDate], 
                                [DownloadTotal], [DownloadApplied], [DownloadFailed], 
                                [UploadTotal], [UploadApplied],[UploadFailed], 
                                [Conflict], [Result], [SchemaVersion], 
                                [ScopeVersion], [ClientId], [EndDate],
                                [Comment],[RunMode],Reviewed,ClientClass,ServerClass) 
                        VALUES 
                               (@SyncHistoryId, @CopeName, @SyncDate, 
                                @DownloadTotal, @DownloadApplied, @DownloadFailed, 
                                @UploadTotal, @UploadApplied, @UploadFailed, 
                                @Conflict, @Result, @SchemaVersion, 
                                @ScopeVersion, @ClientId,@EndDate,
                                @Comment, @RunMode,@Reviewed,@ClientClass,@ServerClass)", conn, tran);

            command.CommandType = global::System.Data.CommandType.Text;
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SyncHistoryId", global::System.Data.SqlDbType.UniqueIdentifier, 0, global::System.Data.ParameterDirection.Input, 0, 0, "SyncHistoryId", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@CopeName", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "CopeName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SyncDate", global::System.Data.SqlDbType.DateTime, 0, global::System.Data.ParameterDirection.Input, 0, 0, "SyncDate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadTotal", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadTotal", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadApplied", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadApplied", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadFailed", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadFailed", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadTotal", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadTotal", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadApplied", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadApplied", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadFailed", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadFailed", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Conflict", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Conflict", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Result", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Result", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SchemaVersion", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "SchemaVersion", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ScopeVersion", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "ScopeVersion", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ClientId", global::System.Data.SqlDbType.UniqueIdentifier, 0, global::System.Data.ParameterDirection.Input, 0, 0, "ClientId", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@EndDate", global::System.Data.SqlDbType.DateTime, 0, global::System.Data.ParameterDirection.Input, 0, 0, "EndDate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Comment", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Comment", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@RunMode", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "RunMode", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Reviewed", global::System.Data.SqlDbType.Bit, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Reviewed", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ClientClass", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "ClientClass", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@ServerClass", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "ServerClass", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            //
            return command;

        }

        private static SqlCommand CreateInsertDepenCommand(SqlConnection conn, SqlTransaction tran)
        {
            SqlCommand command = new SqlCommand("", conn, tran);
            command.CommandText = @"INSERT INTO [dbo].[HistoryDependency] ([SyncHistoryId], [TableName], [DependencyTable], [DownloadTotal], [DownloadApplied], [DownloadFailed], [UploadTotal], [UploadApplied], [UploadFailed], [Conflict], [DownloadDelete], [UploadDelete], [DownloadUpdate], [UploadUpdate], [DownloadInsert], [UploadInsert]) VALUES (@SyncHistoryId, @TableName, @DependencyTable, @DownloadTotal, @DownloadApplied, @DownloadFailed, @UploadTotal, @UploadApplied, @UploadFailed, @Conflict, @DownloadDelete, @UploadDelete, @DownloadUpdate, @UploadUpdate, @DownloadInsert, @UploadInsert)";
            command.CommandType = global::System.Data.CommandType.Text;
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@SyncHistoryId", global::System.Data.SqlDbType.UniqueIdentifier, 0, global::System.Data.ParameterDirection.Input, 0, 0, "SyncHistoryId", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@TableName", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "TableName", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DependencyTable", global::System.Data.SqlDbType.NVarChar, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DependencyTable", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadTotal", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadTotal", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadApplied", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadApplied", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadFailed", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadFailed", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadTotal", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadTotal", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadApplied", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadApplied", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadFailed", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadFailed", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@Conflict", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "Conflict", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadDelete", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadDelete", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadDelete", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadDelete", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadUpdate", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadUpdate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadUpdate", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadUpdate", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@DownloadInsert", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "DownloadInsert", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            command.Parameters.Add(new global::System.Data.SqlClient.SqlParameter("@UploadInsert", global::System.Data.SqlDbType.Int, 0, global::System.Data.ParameterDirection.Input, 0, 0, "UploadInsert", global::System.Data.DataRowVersion.Current, false, null, "", "", ""));
            return command;
        }
        public static string CreateDependencyKey(string tableName, string detailTableName, int rowIndex, params string[] primarykeys)
        {
            string key = "NULL";
            if (primarykeys != null && primarykeys.Length > 0)
            {
                key = String.Join("_", primarykeys);
            }
            key = string.Format("KD_{0}_{1}_{2}", tableName, detailTableName, key);
            return key;
        }
        public static string CreateDependencyKey(string tableName, int rowIndex, params string[] primarykeys)
        {
            string key = "NULL";
            if (primarykeys != null && primarykeys.Length > 0)
            {
                key = String.Join("_", primarykeys);
            }
            key = string.Format("KD_{0}_{1}", tableName, key);
            return key;
        }
        public static void AssignParameterValues(SqlCommand command, DataRowView data, DbSyncStage state, SyncContext context)
        {
            SqlParameterCollection commandParameters = command.Parameters;
            for (int i = 0, j = commandParameters.Count; i < j; i++)
            {

                string columnName = commandParameters[i].ParameterName.Substring(1);
                if (!String.IsNullOrEmpty(commandParameters[i].SourceColumn))
                    if (data.DataView.Table.Columns.Contains(commandParameters[i].SourceColumn))
                    {
                        commandParameters[i].Value = data[commandParameters[i].SourceColumn];
                        continue;
                    }


                if (data.DataView.Table.Columns.Contains(columnName))
                    commandParameters[i].Value = data[columnName];
                else
                {

                    if (columnName.Equals("SyncState", StringComparison.InvariantCultureIgnoreCase))
                    {
                        commandParameters[i].Value = state.ToString();
                        continue;
                    }
                    if (columnName.Equals("SynPosition", StringComparison.InvariantCultureIgnoreCase))
                    {
                        commandParameters[i].Value = context.Position.ToString(); ;
                        continue;
                    }
                    if (columnName.Equals("SyncDirection", StringComparison.InvariantCultureIgnoreCase))
                    {
                        commandParameters[i].Value = context.CurrentScope.Direction;
                        continue;
                    }


                    if (columnName.Equals("RowState", StringComparison.InvariantCultureIgnoreCase))
                    {
                        commandParameters[i].Value = data.Row.RowState.ToString();
                        continue;
                    }

                    if (columnName.Equals("SyncTable", StringComparison.InvariantCultureIgnoreCase) && context.CurrentTable != null)
                    {
                        commandParameters[i].Value = context.CurrentTable.TableName;
                        continue;
                    }

                    if (columnName.Equals("ChirlTable", StringComparison.InvariantCultureIgnoreCase) && context.CurrentDepen != null)
                    {
                        commandParameters[i].Value = context.CurrentDepen.TableName;
                        continue;
                    }

                    if (columnName.Equals("ScopeName", StringComparison.InvariantCultureIgnoreCase) && context.CurrentScope != null)
                    {
                        commandParameters[i].Value = context.CurrentScope.ScopeName;
                        continue;
                    }


                    commandParameters[i].Value = DBNull.Value;
                }
            }
        }
        public static KApplyAction TOSSolveConfigCommand(DbApplyChangeFailedEventArgs e, ISyncSessionManager session)
        {
            SqlCommand command = null;
            TableOfScope syncTable = session.Context.CurrentTable;
            try
            {
                Log.Info("Begin  TOSSolveConfigCommand");
                command = new SqlCommand(syncTable.SolveConfigCommand, session.Connection, session.Transaction);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                SqlParameterCollection commandParameters = command.Parameters;

                for (int i = 0, j = commandParameters.Count; i < j; i++)
                {

                    string columnName = commandParameters[i].ParameterName.Substring(1);
                    if (columnName.StartsWith("RV_"))
                    {
                        DataRow data = e.Conflict.RemoteChange.Rows[0];
                        columnName = columnName.Substring(3);

                        if (e.Conflict.RemoteChange.Columns.Contains(columnName))
                            commandParameters[i].Value = data[columnName];

                    }
                    else
                        if (columnName.StartsWith("LV_"))
                        {
                            columnName = columnName.Substring(3);
                            DataRow data = e.Conflict.LocalChange.Rows[0];
                            if (e.Conflict.RemoteChange.Columns.Contains(columnName))
                                commandParameters[i].Value = data[columnName];
                        }
                        else
                        {
                            switch (columnName)
                            {
                                case "ConflicType":
                                    commandParameters[i].Value = e.Conflict.Type.ToString();
                                    break;
                                default:
                                    commandParameters[i].Value = DBNull.Value;
                                    break;
                            }
                        }
                }
                command.ExecuteNonQuery();
                Log.Info("Finish Call Procedure TOSSolveConfigCommand");
                object value = command.Parameters["@Action"].Value;
                Log.InfoFormat("Result:{0}", value);
                if (value == DBNull.Value)
                    return KApplyAction.Continue;
                else
                    return (KApplyAction)Enum.Parse(typeof(KApplyAction), value.ToString());
            }
            catch (Exception ex)
            {
                Log.Info("End TOSSolveConfigCommand with exception:");
                throw ex;
            }
            finally
            {
                if (command != null)
                    command.Dispose();
            }

        }

        public static DataTable GetChildrenData(DataRowView parrentRow, DbSyncStage state, ISyncSessionManager session)
        {
            DependencyTable chilrenTable = session.Context.CurrentDepen;
            Log.InfoFormat("GetChildrenData TableName:{0} Sate:{1} GetChildrenRowsStore:{2}", chilrenTable.TableName, state, chilrenTable.SelectRowCommand);

            if (string.IsNullOrEmpty(chilrenTable.SelectRowCommand))
            {
                Log.InfoFormat("GetChildrenData  GetChildrenRowsStore IS NULL, DetailID:{0} ", chilrenTable.DepenTableId);
                return new DataTable("");
            }
            try
            {
                DataTable tblData = new DataTable(chilrenTable.TableName);
                using (SqlCommand command = new SqlCommand(chilrenTable.SelectRowCommand, session.Connection, session.Transaction))
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                    AssignParameterValues(command, parrentRow, state, session.Context);
                    adapter.Fill(tblData);
                }


                return tblData;


            }
            catch (Exception ex)
            {
                Log.FatalFormat("GetChildrenData Store:{0} Error {1}", chilrenTable.SelectRowCommand, ex.ToString());
                throw;
            }

        }
        //public static KDataBatchHandle GetChildrenDataBatch(DataRowView parrentRow, DbSyncStage state, ISyncSessionManager session)
        //{
        //    DependencyTable chilrenTable = session.Context.CurrentDepen;
        //    Log.InfoFormat("GetChildrenData TableName:{0} Sate:{1} GetChildrenRowsStore:{2}", chilrenTable.TableName, state, chilrenTable.SelectRowCommand);

        //    if (string.IsNullOrEmpty(chilrenTable.SelectRowCommand))
        //    {
        //        Log.InfoFormat("GetChildrenData  GetChildrenRowsStore IS NULL, DetailID:{0} ", chilrenTable.DepenTableId);
        //        return new KDataBatchHandle();
        //    }
        //    try
        //    {

        //        using (SqlCommand command = new SqlCommand(chilrenTable.SelectRowCommand, session.Connection, session.Transaction))
        //        {
        //            command.CommandType = CommandType.StoredProcedure;
        //            command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
        //            AssignParameterValues(command, parrentRow, state, session.Context);

        //            using (SqlDataReader reader = command.ExecuteReader())
        //            {
        //                KDataBatchHandle batchHandle = new KDataBatchHandle();
        //                batchHandle.BatchSize = session.Context.CurrentScope.BatchSize;
        //                batchHandle.Load(reader, session.Context.CurrentDepen.TableName);
        //                return batchHandle;
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        Log.FatalFormat("GetChildrenData Store:{0} Error {1}", chilrenTable.SelectRowCommand, ex.ToString());
        //        throw;
        //    }

        //}


        public static void TOSPreApplyCommand(DataView view, ISyncSessionManager session)
        {
            try
            {
                TableOfScope deff = session.Context.CurrentTable;
                Log.InfoFormat("TOSPreApplyCommand TableName:{0} Total Row:{1} PreApplyStore:{2}", deff.TableName, view.Count, deff.PreApplyCommand);
                using (SqlCommand command = new SqlCommand(deff.PreApplyCommand, session.Connection, session.Transaction))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                    for (int i = 0; i < view.Count; i++)
                    {
                        DataRowView currentRow = view[i];
                        Log.DebugFormat("Try TOSPreApply RowIndex={0} RowState={1}", i, view[i].Row.RowState);
                        DbSyncStage state = GetSyncStateByRowState(currentRow.Row);
                        AssignParameterValues(command, currentRow, state, session.Context);
                        TraceCommandParameter(command);
                        command.ExecuteNonQuery();
                    }

                }
                Log.InfoFormat("End TOSPreApplyCommand");
            }
            catch (Exception ex)
            {
                Log.Fatal("TOSPreApplyCommand Exception:", ex);
                throw ex;
            }
        }
        public static DbSyncStage GetSyncStateByRowState(DataRow row)
        {
            switch (row.RowState)
            {
                case DataRowState.Added:
                    return DbSyncStage.ApplyingInserts;
                case DataRowState.Deleted:
                    return DbSyncStage.ApplyingDeletes;
                case DataRowState.Modified:
                    return DbSyncStage.ApplyingUpdates;
                default:
                    throw new Exception("Unknow row state");
            }
        }
        public static void TOSAfterApplyCommand(DataRowView currentRow, DbSyncStage state, ISyncSessionManager session)
        {
            try
            {
                if (session == null)
                    throw new Exception("Parameter session is null");
                if (session.Context == null)
                    throw new Exception("Property Context of session parameter is null");
                if (session.Context.CurrentScope == null)
                    throw new Exception("Property CurrentScope is null");
                if (session.Context.CurrentTable == null)
                    throw new Exception("Property CurrentTable is null");

                TableOfScope deff = session.Context.CurrentTable;
                Log.InfoFormat("TOSAfterApplyCommand TableName:{0} DbSyncStage:{1} PreApplyStore:{2}", deff.TableName, state, deff.AfterApplyCommand);
                if (string.IsNullOrEmpty(deff.AfterApplyCommand))
                {
                    Log.Info("  Skip execute because AfterApplyCommand is NULL OR EMPTY");
                }
                else
                    using (SqlCommand command = new SqlCommand(deff.AfterApplyCommand, session.Connection, session.Transaction))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                        AssignParameterValues(command, currentRow, state, session.Context);
                        TraceCommandParameter(command);
                        command.ExecuteNonQuery();

                    }
                Log.InfoFormat("End TOSAfterApplyCommand");
            }
            catch (Exception ex)
            {
                Log.Fatal("TOSAfterApplyCommand Exception:", ex);
                throw ex;
            }
        }
        public static void TOSAfterApplyCommand(DataView view, DbSyncStage state, ISyncSessionManager session)
        {
            try
            {
                TableOfScope deff = session.Context.CurrentTable;
                Log.InfoFormat("TOSAfterApplyCommand TableName:{0} DbSyncStage:{1} AfterApplyCommand:{2}", deff.TableName, state, deff.AfterApplyCommand);
                using (SqlCommand command = new SqlCommand(deff.AfterApplyCommand, session.Connection, session.Transaction))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                    int rowIndex = 0;
                    foreach (DataRowView currentRow in view)
                    {
                        Log.DebugFormat("   TOSAfterApplyCommand Rowindex={0}", rowIndex);
                        AssignParameterValues(command, currentRow, state, session.Context);
                        TraceCommandParameter(command);
                        command.ExecuteNonQuery();
                        rowIndex++;
                    }

                }
                Log.InfoFormat("End TOSAfterApplyCommand");
            }
            catch (Exception ex)
            {
                Log.Fatal("TOSAfterApplyCommand Exception:", ex);
                throw ex;
            }
        }

        public static void DeTPreApplyTableCommand(DataRowView parrentRow, DbSyncStage state, ISyncSessionManager session)
        {
            try
            {
                DependencyTable detail = session.Context.CurrentDepen;
                if (string.IsNullOrEmpty(detail.PreApplyTableCommand))
                {
                    Log.WarnFormat("DeTPreApplyTableCommand[Skipped] TableName:{0} DbSyncStage:{1} PreApplyTableCommand Is NULL or EMPTY"
                        , detail.TableName, state);
                    return;
                }

                Log.InfoFormat("DeTPreApplyTableCommand TableName:{0} DbSyncStage:{1} PreApplyTableCommand:{2}", detail.TableName, state, detail.PreApplyTableCommand);

                using (SqlCommand command = new SqlCommand(detail.PreApplyTableCommand, session.Connection, session.Transaction))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                    //command.Parameters.AddRange(DiscoverSpParameterSet(detail.PreApplyStore, true));
                    AssignParameterValues(command, parrentRow, state, session.Context);
                    command.ExecuteNonQuery();

                }
                Log.InfoFormat("End DeTPreApplyTableCommand");

            }
            catch (Exception ex)
            {
                Log.Fatal("DeTPreApplyTableCommand Exception:", ex);

                throw ex;
            }
        }
        public static void DeTPreApplyRowCommand(DataRowView currentRow, DbSyncStage state, ISyncSessionManager session)
        {
            try
            {
                DependencyTable detail = session.Context.CurrentDepen;
                if (string.IsNullOrEmpty(detail.PreApplyTableCommand))
                {
                    Log.WarnFormat("DeTPreApplyRowCommand[Skipped] TableName:{0} DbSyncStage:{1} PreApplyRowCommand Is NULL or EMPTY"
                        , detail.TableName, state);
                    return;
                }
                Log.InfoFormat("DeTPreApplyRowCommand TableName:{0} DbSyncStage:{1} PreApplyRowCommand:{2}", detail.TableName, state, detail.PreApplyRowCommand);

                using (SqlCommand command = new SqlCommand(detail.PreApplyRowCommand, session.Connection, session.Transaction))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                    //command.Parameters.AddRange(DiscoverSpParameterSet(detail.PreApplyStore, true));
                    AssignParameterValues(command, currentRow, state, session.Context);
                    command.ExecuteNonQuery();

                }
                Log.InfoFormat("End DeTPreApplyRowCommand");

            }
            catch (Exception ex)
            {
                Log.Fatal("DeTPreApplyRowCommand Exception:", ex);

                throw ex;
            }
        }

        public static int DeTCallApplyRowCommand(DataTable tblData, DbSyncStage state, ISyncSessionManager session)
        {
            DependencyTable detail = session.Context.CurrentDepen;
            int rowIndex = 0;
            try
            {
                Log.InfoFormat("Begin Call ApplyStore Dependency TableName:{0} DbSyncStage:{1} ApplyStore:{2}", detail.TableName, state, detail.ApplyRowCommand);
                if (!string.IsNullOrEmpty(detail.ApplyRowCommand))
                    using (SqlCommand command = new SqlCommand(detail.ApplyRowCommand, session.Connection, session.Transaction))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        // command.Parameters.AddRange(DiscoverSpParameterSet(detail.ApplyStore, true));
                        command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                        foreach (DataRowView currentRow in tblData.DefaultView)
                        {
                            KeeperSyncUtility.DeTPreApplyRowCommand(currentRow, state, session);
                            AssignParameterValues(command, currentRow, state, session.Context);
                            TraceCommandParameter(command);
                            command.ExecuteNonQuery();
                            rowIndex++;
                        }
                    }
                else
                {
                    Log.Info("DeTCallApplyRowCommand  IS NULL ==> Using Adapter Builder");
                    Log.Info("DeTCallApplyRowCommand  IS NULL ==> Not Call PreApplyRowCommand");
                    using (SqlCommand cmd = new SqlCommand("Select * from " + detail.TableName, session.Connection, session.Transaction))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            using (SqlCommandBuilder builderCmd = new SqlCommandBuilder(adapter))
                            {
                                adapter.InsertCommand = builderCmd.GetInsertCommand();
                                foreach (DataRow currentRow in tblData.Rows)
                                {
                                    if (tblData.Columns.Contains("RState"))
                                    {
                                        Log.Info("Found RState columns in dependency table ==> Set RowSate by RState value");
                                        DataRowState stateTemp = (DataRowState)Enum.Parse(typeof(DataRowState), currentRow["RState"].ToString());

                                        switch (stateTemp)
                                        {
                                            case DataRowState.Added:
                                                currentRow.SetAdded();
                                                break;
                                            case DataRowState.Deleted:
                                                currentRow.Delete();
                                                break;
                                            case DataRowState.Modified:
                                                currentRow.SetModified();
                                                break;
                                        }
                                    }
                                    else
                                        if (currentRow.RowState == DataRowState.Unchanged || currentRow.RowState == DataRowState.Modified)
                                            currentRow.SetAdded();
                                }
                                adapter.Update(tblData);
                                rowIndex = tblData.Rows.Count;
                            }

                        }
                    }
                }
                return rowIndex;
            }
            catch (Exception ex)
            {
                Log.Fatal(String.Format("CallApplyStore Exception RowIndex:{0}/Total Row:{1}", rowIndex, tblData.Rows.Count), ex);
                throw ex;
            }
        }

        public static void DeTApplyRowCommand(DataRowView currentRow, DbSyncStage state, ISyncSessionManager session)
        {
            DependencyTable detail = session.Context.CurrentDepen;
            try
            {
                Log.InfoFormat("Call DeTApplyRowCommand  Version Row Dependency Table:{0} DbSyncStage:{1} ApplyStore:{2}", detail.TableName, state, detail.ApplyRowCommand);
                if (!string.IsNullOrEmpty(detail.ApplyRowCommand))
                    using (SqlCommand command = new SqlCommand(detail.ApplyRowCommand, session.Connection, session.Transaction))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        //command.Parameters.AddRange(DiscoverSpParameterSet(detail.ApplyStore, true));
                        command.Parameters.AddRange(SqlHelperParameterCache.GetSpParameterSet(CommonVariable.SqlConnectionString, command.CommandText));
                        AssignParameterValues(command, currentRow, state, session.Context);
                        command.ExecuteNonQuery();
                    }
                else
                {
                    Log.Info("DeTApplyRowCommand Version Row ApplyStore IS NULL ==> Using Adapter Builder");
                    using (SqlCommand cmd = new SqlCommand("Select * from " + detail.TableName, session.Connection, session.Transaction))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            using (SqlCommandBuilder builderCmd = new SqlCommandBuilder(adapter))
                            {

                                currentRow.Row.SetAdded();

                                adapter.Update(new DataRow[] { currentRow.Row });
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Log.Fatal("DeTApplyRowCommand Version Row Exception ", ex);

                throw ex;
            }
        }
        public static void TraceCommandParameter(SqlCommand command)
        {
            if (Log.IsDebugEnabled)
            {
                Log.DebugFormat("Trace  Command Parameter CommandText {0}", command.CommandText);
                foreach (SqlParameter p in command.Parameters)
                {
                    Log.DebugFormat("       Parameter Name:{0} Value:{1}", p.ParameterName, p.Value == DBNull.Value ? "DBNull" : p.Value);
                }
                Log.Debug("--End Trace Command Parameter");
            }
        }

        #region ISyncConfigManager Members


        public static void CreateScopeDescription(Microsoft.Synchronization.Data.DbSyncScopeDescription scopeDescription, ScopeInfoEx scope)
        {
            using (
                SqlConnection conn = new SqlConnection(CommonVariable.SqlConnectionString))
            {
                SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning(conn, scopeDescription);
                prov.ObjectSchema = scope.Schema;
                if (!prov.ScopeExists(scopeDescription.ScopeName))
                {
                    prov.Apply();
                }
            }
        }
        public static void CreateTemplateDescription(DbSyncScopeDescription scopeDescription, ScopeInfoEx scope)
        {
            using (SqlConnection conn = new SqlConnection(CommonVariable.SqlConnectionString))
            {
                SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning(conn, scopeDescription, SqlSyncScopeProvisioningType.Template);
                prov.ObjectSchema = scope.Schema;
                //prov.PopulateFromTemplate
                if (prov.TemplateExists(scopeDescription.ScopeName))
                {

                    prov.Apply();
                }
            }

        }

        public static Microsoft.Synchronization.Data.DbSyncScopeDescription GetScopeDescription(ScopeInfoEx scope)
        {
            using (SqlConnection conn = new SqlConnection(CommonVariable.SqlConnectionString))
            {

                DbSyncScopeDescription scopeDess = SqlSyncDescriptionBuilder.GetDescriptionForScope(scope.ScopeName, "", scope.Schema, conn);
                return scopeDess;
            }



        }

        public static bool ExistsScope(ScopeInfoEx scope)
        {
            using (SqlConnection conn = new SqlConnection(CommonVariable.SqlConnectionString))
            {

                SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning(conn);
                prov.ObjectSchema = scope.Schema;
                return prov.ScopeExists(scope.ScopeName);
            }

        }

        public static bool ExistsTemplate(ScopeInfoEx scope)
        {

            using (SqlConnection conn = new SqlConnection(CommonVariable.SqlConnectionString))
            {
                SqlSyncScopeProvisioning prov = new SqlSyncScopeProvisioning(conn);
                prov.ObjectSchema = scope.Schema;
                return prov.TemplateExists(scope.TemplateName);
            }

        }

        #endregion

        public static void ValidateScope(ScopeInfoEx scope, ISyncConfigManager proxy)
        {
            SqlConnection conn = new SqlConnection(CommonVariable.SqlConnectionString);
            Log.Info("Check Scope exists");
            if (!string.IsNullOrEmpty(scope.TemplateName))
            {
                Log.InfoFormat("    Check template Exists ( TemplateName ={0})", scope.TemplateName);
                if (!KeeperSyncUtility.ExistsTemplate(scope))
                {
                }

            }
            if (!KeeperSyncUtility.ExistsScope(scope))
            {
                Log.InfoFormat("Client not exists ScopeName [{0}] try get it from sever", scope.ScopeName);
                if (!proxy.ExistsScope(scope))
                {
                    throw new Exception("Not exists ScopeName [" + scope.ScopeName + "] on both client and server");

                }
                Log.Info("  Try get scope from server");
                DbSyncScopeDescription scopeDess = proxy.GetScopeDescription(scope);
                Log.Info("  Try Apply scope to Client");
                KeeperSyncUtility.CreateScopeDescription(scopeDess, scope);
            }
            if (!proxy.ExistsScope(scope))
            {
                Log.InfoFormat("Server not exists ScopeName [{0}]. Try push it from client", scope.ScopeName);
                Log.Info("  Load DbSyncScopeDescription");
                DbSyncScopeDescription clientScopeDess = KeeperSyncUtility.GetScopeDescription(scope);
                Log.Info("  Push to server");
                proxy.CreateScopeDescription(clientScopeDess, scope);
            }
            Log.Info("Finish Check scope");
        }
        public static string GetSecuConnectionString()
        {
            SqlConnectionStringBuilder s = new SqlConnectionStringBuilder(CommonVariable.SqlConnectionString);
            s.Password = "*".PadLeft(s.Password.Length, '*');
            return s.ConnectionString;
        }
    }
}
